﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Entity;
using LeadMaster.DAL;
using LeadMaster.Models;
using System.Data.SqlClient;


namespace LeadMaster.Helpers
{
    public class GenericRepository
    {
        private DunsmoreContext db = new DunsmoreContext();
        public string RuleDescription { get; set; }

        public virtual IEnumerable<Chasers> GetChasers(string query)
        {
            List<Chasers> chasers = db.Chasers.SqlQuery(query).ToList();
            return chasers;
        }

        public virtual List<NextRef> GetNextRef(string query)
        {
            List<NextRef> refs = db.NextRef.SqlQuery(query).ToList();
            return refs;
        }

        public virtual bool RefreshPO(string pid)
        {
          //db.RefreshPO.SqlQuery("exec dbo.RefreshPO @pid='" + pid + "', @resultset=0");
          string conxString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
          SqlConnection conn = new SqlConnection(conxString);
          SqlCommand cmd = new SqlCommand("dbo.RefreshPO");
          cmd.Connection = conn;
          cmd.Connection.Open();
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.AddWithValue("@pid", pid);
          cmd.Parameters.AddWithValue("@resultset", 0);
          object returnVal = cmd.ExecuteScalar();
          //SqlDataReader rdr = cmd.ExecuteReader();
          cmd.Connection.Close();
          return true;
        }

        public virtual bool ResetLockout()
        {
          bool resultset = db.ResetLockout.SqlQuery("exec ResetLockout").FirstOrDefault().result;
          return resultset;
        }

        public virtual IEnumerable<QCOrder> GetQCOrder(string query)
        {
          List<QCOrder> qcorder = db.QCOrder.SqlQuery(query).ToList();
          return qcorder;
        }

        public virtual DataTable CurrentMappings(int blockID, int templateID, int callcentreID)
        {
          string conxString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
          SqlConnection conn = new SqlConnection(conxString);
          SqlCommand cmd = new SqlCommand("dbo.currentmappings");
          cmd.Connection = conn;
          cmd.Connection.Open();
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.AddWithValue("@BlockID", blockID);
          cmd.Parameters.AddWithValue("@templateID", templateID);
          cmd.Parameters.AddWithValue("@callcentreid", callcentreID);
          //object returnVal = cmd.ExecuteScalar();
          SqlDataReader rdr = cmd.ExecuteReader();
          DataTable dt = new DataTable();
          if (rdr.HasRows)
          {
            dt.Load(rdr);  
          }
          cmd.Connection.Close();
          return dt;
        }

        public virtual bool RunExport(int purchaseOrderID)
        {
          try
          {
            string conxString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            SqlConnection conn = new SqlConnection(conxString);
            SqlCommand cmd = new SqlCommand("dbo.runexport");
            cmd.Connection = conn;
            cmd.Connection.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PurchaseOrderID", purchaseOrderID);
            SqlDataReader rdr = cmd.ExecuteReader();
            cmd.Connection.Close();
            return true;
          } 
          catch (Exception)
          {
            return false;
          }
        }

        public virtual bool IsDuplicate(string title, string forename, string surname, string postcode, string address, string telephone, int surveyID)
        {
          try
          {
            string conxString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            SqlConnection conn = new SqlConnection(conxString);
            SqlCommand cmd = new SqlCommand("dbo.IsDuplicate");
            cmd.Connection = conn;
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select dbo.isduplicate(@title,@forename,@surname,@postcode,@address,@telephone,@surveyid)";
            cmd.Parameters.AddWithValue("@title", title);
            cmd.Parameters.AddWithValue("@forename", forename);
            cmd.Parameters.AddWithValue("@surname", surname);
            cmd.Parameters.AddWithValue("@postcode", postcode);
            cmd.Parameters.AddWithValue("@address", address);
            cmd.Parameters.AddWithValue("@telephone", telephone);
            cmd.Parameters.AddWithValue("@surveyid", surveyID);
            object returnVal = cmd.ExecuteScalar();
            cmd.Connection.Close();
            return Convert.ToBoolean(returnVal);
          }
          catch (Exception ex)
          {
            return false;
          }
        }

        public virtual bool CheckValidity(int SurveyID, string parametervalues)
        {
          try
          {
            
            string conxString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            SqlConnection conn = new SqlConnection(conxString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;
            List<SurveyConstraints> ListSurveyConstraints = db.SurveyConstraints
                                                          .Join(db.SSC, a => a.SurveyConstraintsID, b => b.SurveyConstraintsID, (a, b) => new { a, b })
                                                          .Where(m => m.b.SurveyID == SurveyID)
                                                          .Select(s => s.a)
                                                          .ToList();
            List<string> paramvalues = parametervalues.Split(',').ToList();
            bool returnVal=false;
            foreach (var item in ListSurveyConstraints)
            {
              List<string> paramnames=item.ParameterNames.Split(',').ToList();
              cmd.CommandText = item.SqlExpr;
              if (item.Para1!=null)
              {
                cmd.Parameters.AddWithValue(paramnames[0], paramvalues[0]);  //forename
              }
              if (item.Para2 != null)
              {
                cmd.Parameters.AddWithValue(paramnames[1], paramvalues[1]);  //Surname
              }
              if (item.Para3 != null)
              {
                cmd.Parameters.AddWithValue(paramnames[2], paramvalues[2]);  //Telephone
              }
              if (item.Para4 != null)
              {
                cmd.Parameters.AddWithValue(paramnames[3], paramvalues[3]);  //Postcode
              }
              if (item.Para5 != null)
              {
                cmd.Parameters.AddWithValue(paramnames[4], paramvalues[4]);
              }
              returnVal = (bool)cmd.ExecuteScalar();
              if (returnVal)
              {
                this.RuleDescription = item.Description;               
                break;
              }
            }
           
            cmd.Connection.Close();
            return returnVal;
          }
          catch (Exception ex)
          {
            return false;
          }
        }
    }
}